﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using E.Model;
using X.DALSqlServer;

namespace E.DAL
{
    public class ClassDAL
    {
        /// <summary>
        /// 查找班级
        /// </summary>
        /// <param name="model"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <returns></returns>
        public ClassListModel SearchClassByCondition(ClassModel model, int pageSize, int pageIndex,bool bIsSignUp)
        {
            ClassListModel listModel = new ClassListModel();
            int signUpDeal = 0;
            if (bIsSignUp)
                signUpDeal = 1;
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@ClassCode", model.ClassCode),
                new SqlParameter("@CourseName", model.CourseName),
                new SqlParameter("@CourseCategoryID", model.CourseCategoryID),
                new SqlParameter("@AreaID", model.AreaID),
                new SqlParameter("@StartFromDate", model.StartFromDate),
                new SqlParameter("@StartToDate", model.StartToDate),
                new SqlParameter("@EndFromDate", model.EndFromDate),
                new SqlParameter("@EndToDate", model.EndToDate),
                new SqlParameter("@TeacherID", model.TeacherID),
                new SqlParameter("@SignUpDeal", signUpDeal),
                new SqlParameter("@PageSize", pageSize),
                new SqlParameter("@PageIndex", pageIndex)
            };
           
            IList<ShortClassModel> list = new List<ShortClassModel>();
            using (IDataReader dr = SqlHelper.ExecuteDataReaderByStoredProc("sp_searchClassByCondition", parms))
            {
                int totalCount = 0;
                while (dr.Read())
                {
                    if (dr["ID"] == DBNull.Value)
                    {
                        totalCount = Convert.ToInt32(dr["pos"]);
                    }
                    else
                    {
                        ShortClassModel scm = new ShortClassModel();
                        scm.ID = (int)dr["ID"];
                        scm.ClassCode = dr["ClassCode"].ToString();
                        scm.CourseName = dr["CourseName"].ToString();
                        scm.CourseCategoryName = dr["CourseCategoryName"].ToString();
                        scm.StartTime = (DateTime)dr["StartTime"];
                        scm.EndTime = (DateTime)dr["EndTime"];
                        scm.SchoolHour = dr["SchoolHour"] is DBNull ? 0 : (decimal)dr["SchoolHour"];
                        scm.IsFull = (bool)dr["IsFull"];
                        scm.AreaName = dr["AreaName"].ToString();
                        scm.SignUpType = (int)dr["SignUpType"];
                        scm.UserIDArr = dr["UserIDArr"].ToString();
                        if (dr["CourseRelationID"].ToString() == "")
                            scm.CourseRelationID = 0;
                        else
                            scm.CourseRelationID = (int)dr["CourseRelationID"];
                        list.Add(scm);
                    }
                }
                listModel.TotalCount = totalCount;
                listModel.List = list;
                return listModel;
            }
        }

        /// <summary>
        /// 查找班级（课程报名）
        /// </summary>
        /// <param name="model"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <returns></returns>
        public ClassListModel SearchClassByRegister(ClassModel model, int pageSize, int pageIndex)
        {
            ClassListModel listModel = new ClassListModel();
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@CourseName", model.CourseName),
                new SqlParameter("@CourseCategoryID", model.CourseCategoryID),
                new SqlParameter("@AreaID", model.AreaID),
                new SqlParameter("@StartTime", model.StartTime),
                new SqlParameter("@PageSize", pageSize),
                new SqlParameter("@PageIndex", pageIndex)
            };
            IList<ShortClassModel> list = new List<ShortClassModel>();
            using (IDataReader dr = SqlHelper.ExecuteDataReaderByStoredProc("sp_searchClassByRegister", parms))
            {
                int totalCount = 0;
                while (dr.Read())
                {
                    if (dr["ID"] == DBNull.Value)
                    {
                        totalCount = Convert.ToInt32(dr["pos"]);
                    }
                    else
                    {
                        ShortClassModel scm = new ShortClassModel();
                        scm.ID = (int)dr["ID"];
                        scm.CourseRelationID = (int)dr["CourseRelationID"];
                        scm.TeacherName = dr["TeacherName"].ToString();
                        scm.MaxCount = (int)dr["MaxCount"];
                        scm.CurrentCount = (int)dr["CurrentCount"];
                        scm.Description = dr["Description"].ToString();
                        scm.CourseName = dr["CourseName"].ToString();
                        scm.CourseCategoryName = dr["CourseCategoryName"].ToString();
                        scm.StartTime = (DateTime)dr["StartTime"];
                        scm.EndTime = (DateTime)dr["EndTime"];
                        scm.ClassCode = dr["ClassCode"].ToString();
                        scm.IsFull = (bool)dr["IsFull"];
                        scm.SignUpType = (int)dr["SignUpType"];
                        scm.UserIDArr = dr["UserIDArr"].ToString();
                        list.Add(scm);
                    }
                }
                listModel.TotalCount = totalCount;
                listModel.List = list;
                return listModel;
            }
        }

        /// <summary>
        /// 报名选择课程
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="classid"></param>
        /// <param name="courseRelationID"></param>
        /// <returns></returns>
        public int SignUp(int userid, int classid, int courseRelationID,int signupTypeid)
        {
            if (signupTypeid == 2 || signupTypeid == -2)
            {
                IDataParameter[] parms = new IDataParameter[] {
                new SqlParameter("@ClassID", classid),
                new SqlParameter("@UserID", userid),
                new SqlParameter("@CourseRelationID", courseRelationID)
                };

                return SqlHelper.ExecuteNonQueryByProcecdure("sp_updateClassSignUpType", parms);
            }

            else
            {
                IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@UserID", userid),
                new SqlParameter("@ClassID", classid),
                new SqlParameter("@CourseRelationID", courseRelationID)
            };
                return SqlHelper.ExecuteNonQueryByProcecdure("sp_signup", parms);
            }
           
        }

        /// <summary>
        /// 添加班级
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int AddClass(ClassModel model)
        {
            int newClassId = 0;

            string[] studentArray = model.StudentArrayId.Split(new char[] { ';' });
           
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@ClassCode", model.ClassCode),
                new SqlParameter("@StartTime", model.StartTime),
                new SqlParameter("@EndTime", model.EndTime),
                new SqlParameter("@TeacherID", model.TeacherID),
                new SqlParameter("@CourseRelationID", model.CourseRelationID),
                new SqlParameter("@AreaID", model.AreaID),
                new SqlParameter("@MaxCount", model.MaxCount),

                new SqlParameter("@SignUpType",model.SignUpType)
            };
            using (IDataReader dr = SqlHelper.ExecuteDataReaderByStoredProc("sp_addClass", parms))
            {
                while (dr.Read())
                {
                    newClassId = (int)dr["newClassId"];
                }
            }

            foreach (string tempStudentId in studentArray)
            {
                if (tempStudentId != "")
                {
                    parms = new IDataParameter[] { 
                new SqlParameter("@UserID", tempStudentId),
                new SqlParameter("@ClassID", newClassId),
                new SqlParameter("@CourseRelationID", model.CourseRelationID)
                };

                    SqlHelper.ExecuteNonQueryByProcecdure("sp_signup", parms);
                }
            }

            return newClassId;
          
        }

        /// <summary>
        /// 更新班级信息
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int UpdateClass(ClassModel model)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@ID", model.ID),
                //new SqlParameter("@ClassCode", model.ClassCode),
                //new SqlParameter("@StartTime", model.StartTime),
                //new SqlParameter("@EndTime", model.EndTime),
                //new SqlParameter("@TeacherID", model.TeacherID),
                //new SqlParameter("@CourseRelationID", model.CourseRelationID),
                //new SqlParameter("@AreaID", model.AreaID),
                new SqlParameter("@MaxCount", model.MaxCount)
            };
            string sql = "UPDATE E_Class SET MaxCount = @MaxCount WHERE ID = @ID";
            return SqlHelper.ExecuteNonQueryBySql(sql, parms);
        }

        /// <summary>
        /// 获取班级信息
        /// </summary>
        /// <param name="classid"></param>
        /// <returns></returns>
        public ClassModel GetClassInfo(int classid)
        {
            string sql = "SELECT ID, ClassCode, StartTime, EndTime, MaxCount, CurrentCount, CourseName, CertificateID, CertificateName, CourseRelationID, AreaName, TeacherName FROM v_class_t WHERE ID = " + classid;
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                ClassModel model = new ClassModel();
                if (dr.Read())
                {
                    model.ID = (int)dr["ID"];
                    model.StartTime = (DateTime)dr["StartTime"];
                    model.EndTime = (DateTime)dr["EndTime"];
                    model.MaxCount = (int)dr["MaxCount"];
                    model.CurrentCount = (int)dr["CurrentCount"];
                    model.CertificateID = (int)dr["CertificateID"];
                    model.CourseRelationID = (int)dr["CourseRelationID"];
                    model.ClassCode = dr["ClassCode"].ToString();
                    model.CourseName = dr["CourseName"].ToString();
                    model.CertificateName = dr["CertificateName"].ToString();
                    model.AreaName = dr["AreaName"].ToString();
                    model.TeacherName = dr["TeacherName"].ToString();
                }
                else
                {
                    model.ID = -1;
                }
                return model;
            }
        }

        /// <summary>
        /// 获取班级学员列表
        /// </summary>
        /// <param name="classid"></param>
        /// <returns></returns>
        public IList<UserListModel> GetUserListInClass(int classid)
        {
            IList<UserListModel> list = new List<UserListModel>();
            string sql = "SELECT b.UserID, b.Email, b.UserName, b.Gender FROM E_Class2User AS a LEFT OUTER JOIN E_UserInfo AS b ON a.UserID = b.UserID WHERE a.ClassID = " + classid;
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    UserListModel listModel = new UserListModel();
                    if (dr["UserID"].ToString() != "")
                    {
                        listModel.ID = (int)dr["UserID"];
                        listModel.UserName = dr["UserName"].ToString();
                        listModel.Email = dr["Email"].ToString();
                        listModel.Gender = (bool)dr["Gender"];
                        list.Add(listModel);
                    }
                }
                return list;
            }
        }
        /// <summary>
        /// 获取用户信息（用户ID）
        /// </summary>
        /// <param name="userID"></param>
        /// <returns></returns>
        public UserListModel GetUserNameByUserID(string userID)
        {
            UserListModel listModel = new UserListModel();
            string sql = "SELECT UserID,UserName FROM E_UserInfo WHERE UserID = " + userID;
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {

                    listModel.ID = (int)dr["UserID"];
                    listModel.UserName = dr["UserName"].ToString();
                }
            }
            return listModel;

        }
        #region 内训

        /// <summary>
        /// 获取内训班级
        /// </summary>
        /// <param name="searchModel"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <returns></returns>
        public InternalClassListModel GetInternalClassListByCondition(InternalClassSearchModel searchModel, int pageSize, int pageIndex, bool bIsDeal)
        {
            InternalClassListModel listModel = new InternalClassListModel();
            int signUpDeal = 0;
            if (bIsDeal)
                signUpDeal = 1;
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@ClassCode", searchModel.ClassCode),
                new SqlParameter("@CourseID", searchModel.CourseID),
                new SqlParameter("@ProjectID", searchModel.ProjectID),
                new SqlParameter("@DelFlag", searchModel.DelFlag),
                new SqlParameter("@PageSize", pageSize),
                new SqlParameter("@PageIndex", pageIndex),
                new SqlParameter("@SkillGroup", searchModel.SkillGroup),
                new SqlParameter("@SignUpDeal", signUpDeal)
            };
            IList<InternalClassModel> list = new List<InternalClassModel>();
            using (IDataReader dr = SqlHelper.ExecuteDataReaderByStoredProc("sp_internal_getClassListByCondition", parms))
            {
                int totalCount = 0;
                while (dr.Read())
                {
                    if (dr["ID"] == DBNull.Value)
                    {
                        totalCount = Convert.ToInt32(dr["pos"]);
                    }
                    else
                    {
                        InternalClassModel model = new InternalClassModel();
                        model.ID = (int)dr["ID"];
                        model.ClassCode = dr["ClassCode"].ToString();
                        model.CourseID = (int)dr["CourseID"];
                        model.CourseName = dr["CourseName"].ToString();
                        model.ProjectID = (int)dr["ProjectID"];
                        model.ProjectName = dr["ProjectName"].ToString();
                        model.SignUpType = (int)dr["SignUpType"];
                        model.UserIDArr = dr["UserIDArr"].ToString();
                        list.Add(model);
                    }
                }
                listModel.TotalCount = totalCount;
                listModel.List = list;
                return listModel;
            }
        }

        /// <summary>
        /// 获取内训班级信息
        /// </summary>
        /// <param name="classid"></param>
        /// <returns></returns>
        public InternalClassModel GetInternalClassInfoById(int classid)
        {
            InternalClassModel model = new InternalClassModel();
            string sql = "SELECT a.ID, a.ClassCode, a.TeacherID, a.CourseID, a.CourseName, a.ProjectID, a.ProjectName,a.SignUpType, b.Name AS TeacherName FROM v_internal_class AS a INNER JOIN E_Teacher AS b ON a.TeacherID = b.ID WHERE a.ID = " + classid;
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                if (dr.Read())
                {
                    model.ID = (int)dr["ID"];
                    model.ClassCode = dr["ClassCode"].ToString();
                    model.CourseID = (int)dr["CourseID"];
                    model.CourseName = dr["CourseName"].ToString();
                    model.ProjectID = (int)dr["ProjectID"];
                    model.ProjectName = dr["ProjectName"].ToString();
                    model.TeacherName = dr["TeacherName"].ToString();
                    model.SignUpType = (int)dr["SignUpType"];
                }
                else
                    model.ID = -1;
                return model;
            }
        }

        /// <summary>
        /// 获取内训班级学员列表
        /// </summary>
        /// <param name="classid"></param>
        /// <returns></returns>
        public IList<UserListModel> GetUserListInInternalClass(int classid)
        {
            IList<UserListModel> list = new List<UserListModel>();
            string sql = "SELECT b.UserID, b.Email, b.UserName, b.Gender FROM E_Internal_Class2User AS a LEFT OUTER JOIN E_UserInfo AS b ON a.UserID = b.UserID WHERE a.ClassID = " + classid;
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    UserListModel listModel = new UserListModel();
                    if (dr["UserID"].ToString() != "")
                    {
                        listModel.ID = (int)dr["UserID"];
                        listModel.UserName = dr["UserName"].ToString();
                        listModel.Email = dr["Email"].ToString();
                        listModel.Gender = (bool)dr["Gender"];
                        list.Add(listModel);
                    }
                }
                return list;
            }
        }

        /// <summary>
        /// 添加内训班级
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int AddInternalClass(InternalClassModel model)
        {
            int newClassId = 0;

            string[] courseArray = model.CourseArrayId.Split(new char[] { ';' });
            string[] studentArray = model.StudentArrayId.Split(new char[]{';'});
            foreach (string tempCourseId in courseArray)
            {
                IDataParameter[] parms = new IDataParameter[] { 
                    new SqlParameter("@ClassCode", model.ClassCode),
                    new SqlParameter("@TeacherID", model.TeacherID),
                    new SqlParameter("@CourseID", tempCourseId),
                    new SqlParameter("@SignUpType",model.SignUpType)
                };
                using (IDataReader dr = SqlHelper.ExecuteDataReaderByStoredProc("usp_addInternalClass", parms))
                {
                    while (dr.Read())
                    {
                        newClassId = (int)dr["newClassId"];
                    }
                }

                foreach (string tempStudentId in studentArray)
                {
                    if (tempStudentId != "")
                    {
                        parms = new IDataParameter[] { 
                        new SqlParameter("@UserID", tempStudentId),
                        new SqlParameter("@ClassID", newClassId),
                        new SqlParameter("@CourseID", tempCourseId),
                        new SqlParameter("@ProjectID", model.ProjectID)
                    };
                        SqlHelper.ExecuteNonQueryByProcecdure("sp_internal_signup", parms);
                    }
                }
                
            }

            //string sql = "INSERT INTO E_Internal_Class(ClassCode,TeacherID,CourseID)VALUES(@ClassCode,@TeacherID,@CourseID)";
            //return SqlHelper.ExecuteNonQueryBySql(sql, parms);
            return newClassId;
        }
        /// <summary>
        /// 添加内训报名审核
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int AddSignUpStudents(InternalClassModel model)
        {
            int newClassId = 0;
            string tempCourseId = string.Empty;
            string userIDs = string.Empty;
            IList<int> userIDLst = new List<int>();
            string[] studentArray = model.StudentArrayId.Split(new char[] { ';' });
            string sql = "select ID,UserIDArr from E_Internal_Class where ClassCode = '" + model.ClassCode + "' and TeacherID = " + model.TeacherID + " and CourseID=" + model.CourseID;
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    newClassId = (int)dr["ID"];
                    userIDs = dr["UserIDArr"].ToString();
                    userIDLst = E.Web.Common.WebCommon.Convert2IList(userIDs, ';');
                    foreach (string tempStudentId in studentArray)
                    {
                        IDataParameter[] parms = new IDataParameter[] { 
                        new SqlParameter("@UserID", tempStudentId),
                        new SqlParameter("@ClassID", newClassId),
                        new SqlParameter("@CourseID", model.CourseID),
                        new SqlParameter("@ProjectID", model.ProjectID),
                        };
                        int rst = SqlHelper.ExecuteNonQueryByProcecdure("sp_internal_signup", parms);
                        if (rst > 0)
                        {
                            int userID = Convert.ToInt32(tempStudentId);
                            if (userIDLst.Contains(userID))
                            {
                                userIDLst.Remove(userID);
                            }
                            string userIDMods = E.Web.Common.WebCommon.Convert2String(userIDLst, ";");
                            string sqlMod = "update E_Internal_Class set UserIDArr ='" + userIDMods + "' where id=" + newClassId.ToString();
                            SqlHelper.ExecuteNonQueryBySql(sqlMod);
                        }
                    }

                }
            }


            return newClassId;
        }
        /// <summary>
        /// 添加外训报名审核
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int AddSignUpPublicStudents(ClassModel model)
        {
            int newClassId = 0;
            string tempCourseId = string.Empty;
            string userIDs = string.Empty;
            IList<int> userIDLst = new List<int>();
            string[] studentArray = model.StudentArrayId.Split(new char[] { ';' });
            string sql = "select UserIDArr from E_Class where ID = " + model.ID;
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    newClassId = model.ID;
                    userIDs = dr["UserIDArr"].ToString();
                    userIDLst = E.Web.Common.WebCommon.Convert2IList(userIDs, ';');
                    foreach (string tempStudentId in studentArray)
                    {
                        IDataParameter[] parms = new IDataParameter[] { 
                        new SqlParameter("@UserID", int.Parse(tempStudentId)),
                        new SqlParameter("@ClassID", newClassId),
                        new SqlParameter("@CourseRelationID", model.CourseID)
                        };
                        int rst = SqlHelper.ExecuteNonQueryByProcecdure("sp_signup", parms);
                        if (rst > 0)
                        {
                            int userID = Convert.ToInt32(tempStudentId);
                            if (userIDLst.Contains(userID))
                            {
                                userIDLst.Remove(userID);
                            }
                            string userIDMods = E.Web.Common.WebCommon.Convert2String(userIDLst, ";");
                            string sqlMod = "update E_Class set UserIDArr ='" + userIDMods + "' where id=" + newClassId.ToString();
                            SqlHelper.ExecuteNonQueryBySql(sqlMod);
                        }
                    }

                }
            }


            return newClassId;
        }
        /// <summary>
        /// 更新内训班级
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int UpdateInternalClass(InternalClassModel model)
        {
            IDataParameter[] parms = new IDataParameter[] {
                new SqlParameter("@ID", model.ID), 
                new SqlParameter("@ClassCode", model.ClassCode)
            };
            string sql = "UPDATE E_Internal_Class SET ClassCode = @ClassCode WHERE ID = @ID";
            return SqlHelper.ExecuteNonQueryBySql(sql, parms);
        }

        /// <summary>
        /// 报名
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="classid"></param>
        /// <param name="courseid"></param>
        /// <param name="projectid"></param>
        /// <returns></returns>
        public int SignUpInternal(int userid, int classid, int courseid, int projectid, int signuptypeid)
        {
            if (signuptypeid == 2 || signuptypeid == -2)
            {
                IDataParameter[] parms = new IDataParameter[] {
                new SqlParameter("@ClassID", classid),
                new SqlParameter("@UserID", userid),
                new SqlParameter("@CourseID", courseid),
                new SqlParameter("@ProjectID", projectid)
                };

                return SqlHelper.ExecuteNonQueryByProcecdure("usp_updateInternalClassSignUpType", parms);
            }

            else
            {
                IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@UserID", userid),
                new SqlParameter("@ClassID", classid),
                new SqlParameter("@CourseID", courseid),
                new SqlParameter("@ProjectID", projectid)
            };
                return SqlHelper.ExecuteNonQueryByProcecdure("sp_internal_signup", parms);
            }
        }

        /// <summary>
        /// 获取用户报名班级
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="courseid"></param>
        /// <returns></returns>
        public IList<InternalClassModel> GetUserSignedInternalClassList(int userid, int courseid)
        {
            IList<InternalClassModel> list = new List<InternalClassModel>();
            string sql = "SELECT a.ClassID, a.UserID, b.ClassCode, b.TeacherID, b.CourseID, b.CourseName, b.ProjectID, b.ProjectName FROM E_Internal_Class2User AS a INNER JOIN v_internal_class AS b ON a.ClassID = b.ID WHERE b.DelFlag = 0 AND b.IsNeedJoinClass=1 AND a.UserID = " + userid;
            if (courseid != -1)
            {
                sql += " AND b.CourseID = " + courseid;
            }
            StringBuilder sbUnion = new StringBuilder();
            sbUnion.AppendLine("SELECT 0,0,'','',b.ID as CourseID, b.Name AS CourseName, c.CertificateID AS ProjectID, d.Name AS ProjectName ");
            sbUnion.AppendLine("FROM dbo.E_Dict_Course AS b INNER JOIN ");
            sbUnion.AppendLine("dbo.E_Dict_Course2Certificate AS c ON b.id = c.CourseID INNER JOIN ");
            sbUnion.AppendLine("dbo.E_Dict_Certificate AS d ON c.CertificateID = d.ID where 1=1 AND b.IsNeedJoinClass=0 ");

            if (courseid != -1)
            {
                sbUnion.AppendLine(" AND c.CourseID=" + courseid);
            }

            sql = sql + " union all " + sbUnion.ToString();
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    InternalClassModel model = new InternalClassModel();
                    model.ID = (int)dr["ClassID"];
                    model.ClassCode = dr["ClassCode"].ToString();
                    model.CourseID = (int)dr["CourseID"];
                    model.CourseName = dr["CourseName"].ToString();
                    model.ProjectID = (int)dr["ProjectID"];
                    model.ProjectName = dr["ProjectName"].ToString();
                    list.Add(model);
                }
                return list;
            }
        }

        /// <summary>
        /// 获取内训班级用户数量
        /// </summary>
        /// <param name="classid"></param>
        /// <returns></returns>
        public int GetInternalClassUserCount(int classid)
        {
            string sql = "SELECT COUNT(ID) AS Count FROM E_Internal_Class2User WHERE (ClassID = " + classid + ")";
            return Convert.ToInt32(SqlHelper.ExecuteScalarBySql(sql));
        }
     

        #endregion

        #region 内训 公共课

        /// <summary>
        /// 获取内训班级
        /// </summary>
        /// <param name="searchModel"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <returns></returns>
        public InternalPublicClassListModel GetInternalPublicClassListByCondition(InternalPublicClassSearchModel searchModel, int pageSize, int pageIndex)
        {
            InternalPublicClassListModel listModel = new InternalPublicClassListModel();
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@ClassCode", searchModel.ClassCode),
                new SqlParameter("@CourseID", searchModel.CourseID),
                new SqlParameter("@ProjectID", searchModel.ProjectID),
                new SqlParameter("@PageSize", pageSize),
                new SqlParameter("@PageIndex", pageIndex)
            };
            IList<InternalPublicClassModel> list = new List<InternalPublicClassModel>();
            using (IDataReader dr = SqlHelper.ExecuteDataReaderByStoredProc("sp_public_getClassListByCondition", parms))
            {
                int totalCount = 0;
                while (dr.Read())
                {
                    if (dr["ID"] == DBNull.Value)
                    {
                        totalCount = Convert.ToInt32(dr["pos"]);
                    }
                    else
                    {
                        InternalPublicClassModel model = new InternalPublicClassModel();
                        model.ID = (int)dr["ID"];
                        model.ClassCode = dr["ClassCode"].ToString();
                        model.CourseID = (int)dr["CourseID"];
                        model.CourseName = dr["CourseName"].ToString();
                        model.ProjectID = (int)dr["ProjectID"];
                        model.ProjectName = dr["ProjectName"].ToString();
                        list.Add(model);
                    }
                }
                listModel.TotalCount = totalCount;
                listModel.List = list;
                return listModel;
            }
        }

        /// <summary>
        /// 获取内训班级信息
        /// </summary>
        /// <param name="classid"></param>
        /// <returns></returns>
        public InternalPublicClassModel GetInternalPublicClassInfoById(int classid)
        {
            InternalPublicClassModel model = new InternalPublicClassModel();
            string sql = "SELECT ID, ClassCode, CourseID, CourseName, ProjectID, ProjectName FROM v_public_class WHERE ID = " + classid;
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                if (dr.Read())
                {
                    model.ID = (int)dr["ID"];
                    model.ClassCode = dr["ClassCode"].ToString();
                    model.CourseID = (int)dr["CourseID"];
                    model.CourseName = dr["CourseName"].ToString();
                    model.ProjectID = (int)dr["ProjectID"];
                    model.ProjectName = dr["ProjectName"].ToString();
                }
                else
                    model.ID = -1;
                return model;
            }
        }

        /// <summary>
        /// 获取内训班级学员列表
        /// </summary>
        /// <param name="classid"></param>
        /// <returns></returns>
        public IList<UserListModel> GetUserListInInternalPublicClass(int classid)
        {
            IList<UserListModel> list = new List<UserListModel>();
            string sql = "SELECT b.UserID, b.Email, b.UserName, b.Gender FROM E_Public_Class2User AS a LEFT OUTER JOIN E_UserInfo AS b ON a.UserID = b.UserID WHERE a.ClassID = " + classid;
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    UserListModel listModel = new UserListModel();
                    listModel.ID = (int)dr["UserID"];
                    listModel.UserName = dr["UserName"].ToString();
                    listModel.Email = dr["Email"].ToString();
                    listModel.Gender = (bool)dr["Gender"];
                    list.Add(listModel);
                }
                return list;
            }
        }

        /// <summary>
        /// 添加内训班级
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int AddInternalPublicClass(InternalPublicClassModel model)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@ClassCode", model.ClassCode),
                new SqlParameter("@CourseID", model.CourseID)
            };
            string sql = "INSERT INTO E_Public_Class(ClassCode,CourseID)VALUES(@ClassCode,@CourseID)";
            return SqlHelper.ExecuteNonQueryBySql(sql, parms);
        }

        /// <summary>
        /// 更新内训班级
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int UpdateInternalPublicClass(InternalPublicClassModel model)
        {
            IDataParameter[] parms = new IDataParameter[] {
                new SqlParameter("@ID", model.ID), 
                new SqlParameter("@ClassCode", model.ClassCode)
            };
            string sql = "UPDATE E_Public_Class SET ClassCode = @ClassCode WHERE ID = @ID";
            return SqlHelper.ExecuteNonQueryBySql(sql, parms);
        }

        /// <summary>
        /// 报名
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="classid"></param>
        /// <param name="courseid"></param>
        /// <param name="projectid"></param>
        /// <returns></returns>
        public int SignUpInternalPublic(int userid, int classid, int courseid, int projectid)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@UserID", userid),
                new SqlParameter("@ClassID", classid),
                new SqlParameter("@CourseID", courseid),
                new SqlParameter("@ProjectID", projectid)
            };
            return SqlHelper.ExecuteNonQueryByProcecdure("sp_public_signup", parms);
        }

        /// <summary>
        /// 获取用户报名班级
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="courseid"></param>
        /// <returns></returns>
        public IList<InternalPublicClassModel> GetUserSignedInternalPublicClassList(int userid, int courseid)
        {
            IList<InternalPublicClassModel> list = new List<InternalPublicClassModel>();
            string sql = "SELECT a.ClassID, a.UserID, b.ClassCode, b.CourseID, b.CourseName, b.ProjectID, b.ProjectName FROM E_Public_Class2User AS a INNER JOIN v_public_class AS b ON a.ClassID = b.ID WHERE a.UserID = " + userid;
            if (courseid != -1)
            {
                sql += " AND b.CourseID = " + courseid;
            }
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    InternalPublicClassModel model = new InternalPublicClassModel();
                    model.ID = (int)dr["ClassID"];
                    model.ClassCode = dr["ClassCode"].ToString();
                    model.CourseID = (int)dr["CourseID"];
                    model.CourseName = dr["CourseName"].ToString();
                    model.ProjectID = (int)dr["ProjectID"];
                    model.ProjectName = dr["ProjectName"].ToString();
                    list.Add(model);
                }
                return list;
            }
        }

        /// <summary>
        /// 获取内训班级用户数量
        /// </summary>
        /// <param name="classid"></param>
        /// <returns></returns>
        public int GetInternalPublicClassUserCount(int classid)
        {
            string sql = "SELECT COUNT(ID) AS Count FROM E_Public_Class2User WHERE (ClassID = " + classid + ")";
            return Convert.ToInt32(SqlHelper.ExecuteScalarBySql(sql));
        }

        #endregion
    }
}
